﻿using Aspose.Cells;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Net;
using System.Net.Http;
using System.Net.Http.Headers;
using System.Text;
using System.Threading.Tasks;
using System.Web;

namespace MKAdmin.ToolKit
{
    public static class ExcelHelper
    {
        #region 将Excel转换成datatable
        /// <summary>
        /// 将Excel转换成datatable
        /// </summary>
        /// <param name="strFileName">Excel文件完全路径名</param>
        /// <returns>返回datatable</returns>
        public static DataTable ImportToDataTable(ExcelConvertToDataParamater parameter)
        {
            if (parameter.fileName != "")
            {
                Workbook workbook = new Workbook(parameter.fileName);
                Cells cells = workbook.Worksheets[0].Cells;

                return cells.ExportDataTableAsString(0, 0, cells.MaxDataRow + 1, cells.MaxColumn + 1, true);
            }
            else
            {
                return null;
            }
        }
        #endregion

        #region 将DataTable保存到Excel内
        /// <summary>
        /// 将DataTable保存到Excel内
        /// </summary>
        /// <param name="dtSource">数据源</param>
        /// <param name="headerWidth">Excel列宽</param>
        /// <param name="filePath">Excel文件保存路径</param>
        /// <param name="sheetRowMax">excel sheet内最大行记录数(默认:60000)</param>
        public static void Save(DataTable dtSource, string filePath,
            Dictionary<string, double> headerWidth = null,
            int sheetRowMax = 60000)
        {
            Workbook wb = getWorkBookExcel(dtSource, headerWidth, sheetRowMax);
            wb.Save(filePath);
        }
        #endregion

        #region 将DataTable生成Excel文件的数据流
        /// <summary>
        /// 将DataTable生成Excel文件的数据流
        /// </summary>
        /// <param name="dtSource">数据源</param>
        /// <param name="headerWidth">Excel列宽</param>
        /// <param name="dateTimeColumnFormat">时间列格式(默认:yyyy-MM-dd)</param>
        /// <param name="sheetRowMax">excel sheet内最大行记录数(默认:60000)</param>
        /// <returns>返回DataTable生成Excel文件的数据流</returns>
        public static MemoryStream ExportExcelToStream(DataTable dtSource,
            Dictionary<string, double> headerWidth = null,
            int sheetRowMax = 60000)
        {
            Workbook wb = getWorkBookExcel(dtSource, headerWidth, sheetRowMax);
            return wb.SaveToStream();
        }
        #endregion

        #region 用于Web导出
        /// <summary>
        /// 用于Web导出
        /// </summary>
        /// <param name="dtSource"></param>
        /// <param name="fileName">文件名</param>
        /// <param name="headerWidth"></param>
        /// <param name="sheetRowMax"></param>
        public static HttpResponseMessage ExportByWeb(DataTable dtSource, string fileName
            , Dictionary<string, double> headerWidth = null, int sheetRowMax = 60000)
        {
            byte[] bytes = ExportExcelToStream(dtSource, headerWidth, sheetRowMax).GetBuffer();

            //返回一个http响应的消息对象
            if (bytes != null)
            {
                HttpResponseMessage result = new HttpResponseMessage(HttpStatusCode.OK);
                result.Content = new StreamContent(new MemoryStream(bytes));
                result.Content.Headers.ContentType = new MediaTypeHeaderValue("application/octet-stream");
                result.Content.Headers.ContentDisposition = new ContentDispositionHeaderValue("attachment");
                //文件命名        
                result.Content.Headers.ContentDisposition.FileName = DateTime.Now.ToString("yyyyMMddHHmmssfff") + ".xls";

                return result;
            }

            return new HttpResponseMessage(HttpStatusCode.OK);
        }
        #endregion

        #region 将DataTable保存到Excel内并返回Workbook
        /// <summary>
        /// 将DataTable保存到Excel内并返回Workbook
        /// </summary>
        /// <param name="dtSource">数据源</param>
        /// <param name="headerWidth">Excel列宽</param>
        /// <param name="sheetRowMax">excel sheet内最大行记录数</param>
        /// <returns>返回已经被数据源填充的Workbook</returns>
        private static Workbook getWorkBookExcel(DataTable dtSource,
            Dictionary<string, double> headerWidth = null,
            int sheetRowMax = 60000)
        {
            Workbook wb = new Workbook();
            //标题样式
            Style syTitle = new Style();
            syTitle.Font.IsBold = true;
            syTitle.HorizontalAlignment = TextAlignmentType.Center;

            //需要sheet的个数
            int sheetCount = dtSource.Rows.Count / sheetRowMax;
            for (int i = 0; i <= sheetCount; i++)
            {
                //数据源行开始位置
                int rowStartIndex = i * sheetRowMax;
                //数据源行结束位置
                int rowEndIndex = ((i + 1) * sheetRowMax) > dtSource.Rows.Count ?
                    dtSource.Rows.Count : ((i + 1) * sheetRowMax);

                if (rowStartIndex < rowEndIndex)
                {
                    int rowStart = 1;
                    int sheetIndex = wb.Worksheets.Add();
                    Worksheet ws = wb.Worksheets[sheetIndex - 1];
                    //标题
                    for (int j = 0; j < dtSource.Columns.Count; j++)
                    {
                        string columnName = dtSource.Columns[j].ColumnName;
                        ws.Cells[0, j].Value = dtSource.Columns[j].ColumnName;
                        ws.Cells[j].SetStyle(syTitle);
                        //设置excel列宽
                        if (headerWidth != null)
                        {
                            ws.Cells.SetColumnWidth(j, headerWidth[columnName]);
                        }
                    }
                    for (int k = rowStartIndex; k < rowEndIndex; k++)
                    {
                        for (int m = 0; m < dtSource.Columns.Count; m++)
                        {
                            string drValue = dtSource.Rows[k][m].ToString();

                            switch (dtSource.Columns[m].DataType.ToString().ToLower())
                            {
                                case "system.string"://字符串类型
                                    ws.Cells[rowStart, m].Value = drValue;
                                    break;
                                case "system.datetime"://日期类型
                                    DateTime dateV;
                                    DateTime.TryParse(drValue, out dateV);
                                    string dateL;
                                    //如果时分秒都为0，则精确到天，否则精确到秒
                                    if (dateV == DateTime.Parse(dateV.ToString("yyyy-MM-dd")))
                                    {
                                        dateL = dateV.ToString("yyyy-MM-dd");
                                    }
                                    else
                                    {
                                        dateL = dateV.ToString("yyyy-MM-dd HH:mm:ss");
                                    }

                                    ws.Cells[rowStart, m].Value = dateL;
                                    break;
                                case "system.boolean"://布尔型
                                    bool boolV = false;
                                    bool.TryParse(drValue, out boolV);
                                    ws.Cells[rowStart, m].Value = boolV;
                                    break;
                                case "system.int16"://整型
                                case "system.int32":
                                case "system.int64":
                                case "system.byte":
                                    int intV = 0;
                                    int.TryParse(drValue, out intV);
                                    ws.Cells[rowStart, m].Value = intV;
                                    break;
                                case "system.decimal"://浮点型
                                case "system.double":
                                    double doubV = 0;
                                    double.TryParse(drValue, out doubV);
                                    ws.Cells[rowStart, m].Value = doubV;
                                    break;
                                case "system.dbnull"://空值处理
                                    ws.Cells[rowStart, m].Value = "";
                                    break;
                                default:
                                    ws.Cells[rowStart, m].Value = "";
                                    break;
                            }

                        }
                        rowStart++;
                    }
                }
            }

            return wb;
        }
        #endregion
    }

    public class ExcelConvertToDataParamater
    {
        /// <summary>
        /// 文件名称
        /// </summary>
        public string fileName { get; set; }
        /// <summary>
        /// 第几行开始转换
        /// </summary>
        public int firstRow { get; set; } = 0;
        /// <summary>
        /// 第几列开始转换
        /// </summary>
        public int firstColumn { get; set; } = 0;
    }
}
